﻿using CNative.Utilities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Reflection;
using System.Text;

namespace CNative.DbUtils
{
    public static class Funs
    {
        #region 缓存数据处理
        public static ICacheHelper CacheHelper = new MemoryCacheHelper();
        /// <summary>
        /// 从缓存中获取数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key">缓存项的唯一标识符</param>
        /// <param name="cachePopulate">>获取缓存值的操作(当缓存不存在相应的数据时，执行此方法获取数据)</param>
        /// <param name="expiresTime">缓存时长(分钟), enum ExpiresTime</param>
        /// <param name="isSliding">是否滑动过期（如果在过期时间内有操作，则以当前时间点延长过期时间）</param>
        /// <returns></returns>
        public static T CacheGet<T>(string key, Func<T> cachePopulate, ExpiresTime expiresTime = ExpiresTime.Minutes_30, Func<T, bool> checkPopulate = null)
        {
            return CacheHelper.Get<T>(key, cachePopulate, TimeSpan.FromMinutes(expiresTime.GetHashCode()), checkPopulate);
        }
        /// <summary>
        /// 设置缓存数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key">缓存项的唯一标识符</param>
        /// <param name="value">该缓存项的数据</param>
        /// <param name="expiresTime">缓存时长(分钟), enum ExpiresTime</param>
        /// <param name="isSliding">是否滑动过期（如果在过期时间内有操作，则以当前时间点延长过期时间）</param>
        /// <returns></returns>
        public static bool CacheSet(string key, object value, ExpiresTime expiresTime = ExpiresTime.Minutes_30)
        {
            return CacheHelper.Set(key, value, TimeSpan.FromMinutes(expiresTime.GetHashCode()));
        }
        /// <summary>
        /// 删除缓存
        /// </summary>
        /// <param name="key">缓存项的唯一标识符</param>
        /// <returns></returns>
        public static bool CacheRemove(string key)
        {
            return MemoryCacheHelper.Instance.Remove(key);
        }
        #endregion

        #region FormatFieldName
        /// <summary>
        /// 格式化字段
        /// </summary>
        /// <param name="dicTableAlias">表别名字典</param>
        /// <param name="classType"></param>
        /// <param name="fieldName"></param>
        /// <param name="suffixLeft">前缀</param>
        /// <param name="suffixRigh">后缀</param>
        /// <returns></returns>
        public static string FormatFieldName(Type classType, string fieldName, string suffixLeft, string suffixRigh)
        {
            var tableAlias = "";
            if (classType != null)
            {
                tableAlias = SqlAliasHelper.GetTableAlias(classType);
            }
            return FormatFieldName(tableAlias, fieldName, suffixLeft, suffixRigh);
        }
        /// <summary>
        /// 格式化字段
        /// </summary>
        /// <param name="dicTableAlias">表别名字典</param>
        /// <param name="dbColumn">列实体</param>
        /// <param name="suffixLeft">前缀</param>
        /// <param name="suffixRigh">后缀</param>
        /// <returns></returns>
        public static string FormatFieldName(DbColumnInfo dbColumn, string suffixLeft, string suffixRigh)
        {
            if (dbColumn == null)
            {
                return "";
            }
            return FormatFieldName(dbColumn.PropertyInfo?.DeclaringType, dbColumn.Name, suffixLeft, suffixRigh);
        }
        /// <summary>
        /// 格式化字段
        /// </summary>
        /// <param name="dicTableAlias">表别名字典</param>
        /// <param name="classType"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static string FormatFieldName(Type classType, string fieldName, SqlEntity sqlent)
        {
            var tableAlias = "";
            if (classType != null)
            {
                tableAlias = SqlAliasHelper.GetTableAlias(classType);
            }
            return FormatFieldName(tableAlias, fieldName, sqlent?.DbHelper?.SqlDbProvider.SuffixLeft, sqlent?.DbHelper?.SqlDbProvider.SuffixRigh);
        }
        /// <summary>
        /// 格式化字段
        /// </summary>
        /// <param name="dicTableAlias">表别名字典</param>
        /// <param name="dbColumn">列实体</param>
        /// <returns></returns>
        public static string FormatFieldName(DbColumnInfo dbColumn, SqlEntity sqlent)
        {
            if (dbColumn == null)
            {
                return "";
            }
            return FormatFieldName(dbColumn.PropertyInfo?.DeclaringType, dbColumn.Name, sqlent?.DbHelper?.SqlDbProvider.SuffixLeft, sqlent?.DbHelper?.SqlDbProvider.SuffixRigh);
        }
        /// <summary>
        /// 格式化字段
        /// </summary>
        /// <param name="dicTableAlias">表别名字典</param>
        /// <param name="dbColumn">列实体</param>
        /// <returns></returns>
        public static string FormatFieldName(DbColumnInfo dbColumn, BaseProvider dbProvider)
        {
            if (dbColumn == null)
            {
                return "";
            }
            return FormatFieldName(dbColumn.PropertyInfo?.DeclaringType, dbColumn.Name, dbProvider?.SuffixLeft, dbProvider?.SuffixRigh);
        }
        /// <summary>
        /// 格式化字段
        /// </summary>
        /// <param name="tableAlias">表别名字典</param>
        /// <param name="fieldName">字段名</param>
        /// <returns></returns>
        public static string FormatFieldName(string tableAlias, string fieldName, BaseProvider dbProvider)
        {
            return FormatFieldName(tableAlias, fieldName, dbProvider?.SuffixLeft, dbProvider?.SuffixRigh);
        }
        /// <summary>
        /// 格式化字段
        /// </summary>
        /// <param name="tableAlias">表别名字典</param>
        /// <param name="fieldName">字段名</param>
        /// <returns></returns>
        public static string FormatFieldName(string tableAlias, string fieldName, SqlEntity sqlent)
        {
            return FormatFieldName(tableAlias, fieldName, sqlent?.DbHelper?.SqlDbProvider.SuffixLeft, sqlent?.DbHelper?.SqlDbProvider.SuffixRigh);
        }
        /// <summary>
        /// 格式化字段
        /// </summary>
        /// <param name="tableAlias">表别名字典</param>
        /// <param name="fieldName">字段名</param>
        /// <param name="suffixLeft">前缀</param>
        /// <param name="suffixRigh">后缀</param>
        /// <returns></returns>
        public static string FormatFieldName(string tableAlias, string fieldName, string suffixLeft, string suffixRigh)
        {
            var fieldMeta = "{0}" + suffixLeft + "{1}" + suffixRigh + " ";
            tableAlias = tableAlias.NullToStr().Replace(".", "");
            tableAlias = tableAlias.Length > 0 ? tableAlias + "." : "";
            return string.Format(fieldMeta, tableAlias, fieldName.Trim());
        }
        #endregion

        #region GetDbTableInfo/GetPropertys
        public static List<PropertyInfo> GetKeyPropertys<T>(IDbHelper dbHelper) where T : class, new()
        {
            var tb = GetDbTableInfo<T>(dbHelper);
            if (tb == null || tb.TableInfo == null || !tb.TableInfo.Exists(e => e.IsPrimaryKey))
            {
                var properties = Utilities.FastReflection.FastGetPropertyList<T>()
                    .Where(x => x.GetDbFieldMap()?.IsPrimaryKey == true)
                    .ToList();

                return properties;
            }
            else if (tb.TableInfo.Exists(e => e.IsPrimaryKey))
            {
                var properties = Utilities.FastReflection.FastGetPropertyList<T>()
                    .Where(x => tb.TableInfo.Exists(e => e.IsPrimaryKey && e.Name.Trim().ToUpper() == x.Name.Trim().ToUpper()))
                    .ToList();

                return properties;
            }
            else return new List<PropertyInfo>();
        }

        /// <summary>
        /// 通过连接字符串和表名获取数据库表的信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public static DbTableInfo GetDbTableInfo<T>(this IDbHelper dbHelper) where T : class, new()
        {
            var tableType = typeof(T);
            return dbHelper.GetDbTableInfo(tableType);
        }
        /// <summary>
        /// 通过连接字符串和表名获取数据库表的信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public static DbTableInfo GetDbTableInfo(this IDbHelper dbHelper, Type tableType)
        {
            if (tableType.IsNullOrEmpty()) return null;

            return CacheGet(tableType.FullName, () =>
            {
                var tb = GetDbTableName(tableType);
                var cols = GetDbDbColumnInfo(dbHelper, tb.TableName, tb.Schema);
                cols?.ForEach(c =>
                {
                    c.IsInTable = true;
                    var col = tb.GetColumn(c.Name);
                    if (col != null)
                    {
                        col.IsInTable = c.IsInTable;
                        col.ColumnId = c.ColumnId;
                        col.Name = c.Name;
                        col.Type = c.Type;
                        col.IsIdentity = c.IsIdentity;
                        col.IsNullable = c.IsNullable;
                        col.IsPrimaryKey = c.IsPrimaryKey;
                        col.MaxLength = c.MaxLength;
                    }
                    else tb.TableInfo.Add(c);
                });
                //tb.TableInfo = GetDbDbColumnInfo(dbHelper, tb.TableName, tb.Schema);

                return tb;
            });
        }
        public static string GetSchemaTableName<T>(this IDbHelper dbHelper) where T : class, new()
        {
            return GetSchemaTableName(dbHelper, typeof(T));
        }
        public static string GetSchemaTableName(this IDbHelper dbHelper, Type tableType)
        {
            if (dbHelper == null)
            {
                return "";
            }
            return dbHelper.SqlDbProvider.GetSchemaTableName(tableType);
        }
        //static DbTableInfo GetDbTableName<T>() where T : class, new()
        //{
        //    var tableType = typeof(T);
        //    return GetDbTableName(tableType);
        //}
        static DbTableInfo GetDbTableName(Type tableType)
        {
            DbTableInfo tb = new DbTableInfo() { TableName = tableType.Name };
            var EntityTableAttributes = tableType.GetCustomAttributes(typeof(TableMapAttribute), false);
            if (EntityTableAttributes != null && EntityTableAttributes.Length > 0)
            {
                var entityTableAttribute = EntityTableAttributes[0] as TableMapAttribute;
                tb = new DbTableInfo()
                {
                    TableName = entityTableAttribute.TableName.Trim().ToUpper(),
                    Schema = entityTableAttribute.Schema
                };
                if (tb.TableName.IsNullOrEmpty())
                    throw new Exception(tableType.FullName + ",该实体没有表名标识！请使用[TableMap(TableName=\"表名\",Schema=\"库名\")]标识！");
                //if (tb.Schema.IsNullOrEmpty())
                //    throw new Exception(tableType.FullName + ",该实体没有库名标识！请使用[TableMap(TableName=\"表名\",Schema=\"库名\")]标识！");
            }
            //else
            //{
            //    throw new Exception(tableType.FullName + ",该实体没有表名标识！请使用[TableMap(TableName=\"表名\",Schema=\"库名\")]标识！");
            //}

            // DbColumnInfo
            var columns = new List<DbColumnInfo>();
            var props = Utilities.FastReflection.FastGetPropertyList(tableType);
            foreach (var prop in props)
            {
                var col = new DbColumnInfo(prop);
                columns.Add(col);
                var attr = prop.GetCustomAttribute<DbFieldMapAttribute>();
                if (attr != null)
                {
                    col.Name = attr.CloumnName;
                    if (attr.Description.IsNotNullOrEmpty())
                        col.Description = attr.Description;
                    col.Type = attr.Type;
                    col.IsIdentity = attr.IsIdentity;
                    col.IsNullable = attr.IsNullable;
                    col.IsPrimaryKey = attr.IsPrimaryKey;
                    col.MaxLength = attr.MaxLength;
                }
                else
                {
                    col.Name = prop.Name;
                }
            }
            tb.TableInfo = columns;

            return tb;
        }
        /// <summary>
        /// 通过连接字符串和表名获取数据库表的信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public static List<DbColumnInfo> GetDbDbColumnInfo(IDbHelper dbHelper, string tableName, string DbName)
        {
            if (tableName.IsNullOrEmpty())
                return new List<DbColumnInfo>();
            //---------------------------------------------------------------------
            var sqle = dbHelper.SqlDbProvider.GetDbTableInfoSQL(dbHelper, tableName, DbName);
            if (sqle == null) return new List<DbColumnInfo>();
            var list = new List<DbColumnInfo>();
            if (dbHelper.DBType == DatabaseType.Sqlite)
            {
                var dt = dbHelper.QueryDataTable(sqle);
                if (dt != null && dt.Rows.Count > 0)
                {
                    dt.Columns.Add(new DataColumn("table_name"));
                    dt.Columns.Add(new DataColumn("column_type"));
                    dt.Columns.Add(new DataColumn("MaxLength"));
                    foreach (DataColumn column in dt.Columns)
                    {
                        if (column.ColumnName.ToLower() == "cid") column.ColumnName = "ColumnId";
                        else if (column.ColumnName.ToLower() == "notnull") column.ColumnName = "IsNullable";
                        else if (column.ColumnName.ToLower() == "pk") column.ColumnName = "IsPrimaryKey";
                    }
                    foreach (DataRow dr in dt.Rows)
                    {
                        try
                        {
                            var typestr = dr["type"].NullToStr();
                            var typestrs = typestr.Split('(');
                            dr["table_name"] = "tableName";
                            dr["column_type"] = typestr;
                            dr["type"] = typestrs[0];
                            try
                            {
                                dr["MaxLength"] = typestrs.Length > 1 ? typestrs[1].Split(',', '(', ')')[0] : "0";
                            }
                            catch { }
                        }
                        catch { }
                    }
                    list = EntityHelper.DataTableToList<DbColumnInfo>(dt);
                }
            }
            else if (dbHelper.DBType == DatabaseType.MsAccess)
            {
                return GetTableFieldNameList(dbHelper, $"{tableName}");
            }
            else
            {
                list = dbHelper.Query<DbColumnInfo>(sqle);
            }
            return list;
        }
        #region OleDb GetTableNameList/GetTableFieldNameList
        /// <summary>
        /// OleDb取所有表名
        /// </summary>
        /// <returns></returns>
        public static List<string> GetTableNameList(IDbHelper dbHelper, string stype = "TABLE")
        {
            List<string> list = new List<string>();
            var Conn = new OleDbConnection(dbHelper.ConnectString);
            try
            {
                if (Conn.State == ConnectionState.Closed)
                    Conn.Open();
                //DataTable shemaTable = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                DataTable dt = Conn.GetSchema("Tables");
                foreach (DataRow row in dt.Rows)
                {
                    if (row[3].ToString() == stype)
                        list.Add(row[2].ToString());
                }
                return list;
            }
            catch (Exception e)
            {
                return list;
                //throw e;
            }
            finally { if (Conn.State == ConnectionState.Open) Conn.Close(); Conn.Dispose(); }
        }
        /// <summary>
        /// OleDb取指定表所有字段名称
        /// </summary>
        /// <returns></returns>
        public static List<DbColumnInfo> GetTableFieldNameList(IDbHelper dbHelper, string TableName)
        {
            var list = new List<DbColumnInfo>();
            var Conn = new OleDbConnection(dbHelper.ConnectString);
            try
            {
                if (Conn.State == ConnectionState.Closed)
                    Conn.Open();

                //var primary_Keys = new List<string>();
                //var schemaTable = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new Object[] { null, null, TableName });
                //if (schemaTable != null && schemaTable.Rows.Count > 0)
                //{
                //    for (int i = 0; i < schemaTable.Rows.Count; i++)
                //    {
                //        try
                //        {
                //            var colName = schemaTable.Rows[i].GetColumnValue("COLUMN_NAME").NullToStr();
                //            if (colName.IsNotNullOrEmpty()) primary_Keys.Add(colName);
                //        }
                //        catch { }
                //    }
                //}
                //var dt = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, TableName, null });
                //if (dt != null && dt.Rows.Count > 0)
                //{
                //    for (int i = 0; i < dt.Rows.Count; i++)
                //    {
                //        var col = new DbColumnInfo();
                //        try
                //        {
                //            col.Name = dt.Rows[i].GetColumnValue("COLUMN_NAME").NullToStr();
                //            col.Description = dt.Rows[i].GetColumnValue("DESCRIPTION").NullToStr();
                //            col.ColumnId = dt.Rows[i].GetColumnValue("ORDINAL_POSITION").ToInt_();
                //            col.MaxLength = dt.Rows[i].GetColumnValue("CHARACTER_MAXIMUM_LENGTH").ToLong_();
                //            col.IsPrimaryKey = primary_Keys.Contains(col.Name);
                //            col.IsIdentity = col.IsPrimaryKey && dt.Rows[i].GetColumnValue("COLUMN_FLAGS").ToInt_() == 90;
                //            col.IsNullable = dt.Rows[i].GetColumnValue("IS_NULLABLE").ToBool_();
                //            var providerType = dt.Rows[i].GetColumnValue("DATA_TYPE").ToInt_();
                //            col.Type = ((OleDbType)providerType).ToString();
                //        }
                //        catch { }
                //        list.Add(col);
                //    }
                //}

                var adapter = new System.Data.OleDb.OleDbDataAdapter($"SELECT TOP 1 * FROM {dbHelper.SqlDbProvider.SuffixLeft}{TableName}{dbHelper.SqlDbProvider.SuffixRigh}", Conn);
                // Fill the DataTable, retrieving all the schema information.
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                adapter.MissingMappingAction =  MissingMappingAction.Passthrough;
                DataTable table = new DataTable();
                adapter.Fill(table);
                // Create the DataTableReader, and close it when done.
                using (DataTableReader reader = new DataTableReader(table))
                {
                    var dt = reader.GetSchemaTable();
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            var col = new DbColumnInfo();
                            try
                            {
                                col.Name = dt.Rows[i].GetColumnValue("ColumnName").NullToStr();
                                col.ColumnId = dt.Rows[i].GetColumnValue("ColumnOrdinal").ToInt_();
                                col.MaxLength = dt.Rows[i].GetColumnValue("ColumnSize").ToLong_();
                                col.IsPrimaryKey = dt.Rows[i].GetColumnValue("IsKey").ToBool_();
                                col.IsIdentity = dt.Rows[i].GetColumnValue("IsAutoIncrement").ToBool_();
                                col.IsNullable = dt.Rows[i].GetColumnValue("AllowDBNull").ToBool_();
                                var providerType = dt.Rows[i].GetColumnValue("ProviderType").ToInt_();
                                col.Type = ((OleDbType)providerType).ToString();
                                col.IsInTable = true;
                            }
                            catch { }
                            list.Add(col);
                        }
                    }
                }

                var dt2 = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, TableName, null });
                if (dt2 != null && dt2.Rows.Count > 0)
                {
                    for (int i = 0; i < dt2.Rows.Count; i++)
                    {
                        try
                        {
                            var colName = dt2.Rows[i].GetColumnValue("COLUMN_NAME").NullToStr();
                            var col = list.Find(f => f.Name == colName);
                            if (col != null)
                            {
                                var providerType = dt2.Rows[i].GetColumnValue("DATA_TYPE").ToInt_();
                                col.Type = ((OleDbType)providerType).ToString();
                            }
                        }
                        catch { }
                    }
                }
                adapter.Dispose();

                return list;
            }
            catch (Exception e)
            {
                return list;
                //throw e; 
            }
            finally
            {
                if (Conn.State == ConnectionState.Open)
                    Conn.Close();
                Conn.Dispose();
            }
        }
        #endregion
        #endregion

        #region GetColumnName
        /// <summary>
        /// 获取字段名称
        /// </summary>
        /// <param name="member"></param>
        /// <returns></returns>
        public static string GetColumnName(this System.Reflection.MemberInfo member)
        {
            if (member == null) return "";
            var attr = member.GetDbFieldMap();
            if (attr != null && attr.CloumnName.IsNotNullOrEmpty())
            {
                return attr.CloumnName;
            }
            return member.Name;
        }
        /// <summary>
        /// 获取字段名称
        /// </summary>
        /// <param name="member"></param>
        /// <returns></returns>
        public static string GetColumnName(this System.Reflection.PropertyInfo member)
        {
            return GetColumnName(member as System.Reflection.MemberInfo);
        }
        /// <summary>
        /// 获取字段名称
        /// </summary>
        /// <param name="member"></param>
        /// <returns></returns>
        public static string GetColumnName(this System.Reflection.FieldInfo member)
        {
            return GetColumnName(member as System.Reflection.MemberInfo);
        }
        #endregion
        #region GetDbFieldMap
        /// <summary>
        /// 获取字段的映射特性
        /// </summary>
        /// <param name="member"></param>
        /// <returns></returns>
        public static DbFieldMapAttribute GetDbFieldMap(this System.Reflection.MemberInfo member)
        {
            return member?.GetCustomAttribute<DbFieldMapAttribute>();
        }
        /// <summary>
        /// 获取字段的映射特性
        /// </summary>
        /// <param name="member"></param>
        /// <returns></returns>
        public static DbFieldMapAttribute GetDbFieldMap(this System.Reflection.PropertyInfo member)
        {
            return GetDbFieldMap(member as System.Reflection.MemberInfo);
        }
        /// <summary>
        /// 获取字段的映射特性
        /// </summary>
        /// <param name="member"></param>
        /// <returns></returns>
        public static DbFieldMapAttribute GetDbFieldMap(this System.Reflection.FieldInfo member)
        {
            return GetDbFieldMap(member as System.Reflection.MemberInfo);
        }
        #endregion
    }
}
